-- 0 Database name
-- create database
DROP DATABASE if exists {0};
create database {0};

-- create tables
use {0};
DROP TABLE IF EXISTS `dcrole`;
CREATE TABLE  `dcrole` (
  `DcRoleId` int(11) NOT NULL auto_increment,
  `RoleName` varchar(254) NOT NULL,
  
  PRIMARY KEY  USING BTREE (`DcRoleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `department`;
CREATE TABLE  `department` (
  `DepartmentId` int(11) NOT NULL auto_increment,
  `Name` varchar(254) NOT NULL,
  PRIMARY KEY  USING BTREE (`DepartmentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `dcuser`;
CREATE TABLE  `dcuser` (
  `DcUserId` int(11) NOT NULL auto_increment,
  `Login` varchar(254) NOT NULL,
  `Password` varchar(254) NOT NULL,
  `FullName` varchar(254) NOT NULL,
  `AutomaticPass` tinyint(1) NOT NULL,
  `AllowDeputyNotification` tinyint(1) NOT NULL,
  `Department` int(11) NOT NULL,
  `InstantMessaging` tinyint(1) NOT NULL,
  `ImAddress` varchar(254) NOT NULL,
  `ChangePwdNextTime` tinyint(1) NOT NULL,
  `ExternalAuthorization` tinyint(1) NOT NULL,
  `ExternalIdentity` varchar(254) NOT NULL,
  PRIMARY KEY  USING BTREE (`DcUserId`),
  KEY `login` USING BTREE (`Login`),
  KEY `Department` USING BTREE (`Department`),
  CONSTRAINT `FK_dcuser_1` FOREIGN KEY (`Department`) REFERENCES `Department` (`DepartmentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `deputy`;
CREATE TABLE  `deputy` (
  `DeputyId` int(11) NOT NULL auto_increment,
  `DcUser` int(11) NOT NULL,
  `Deputy` int(11) NOT NULL,
  `Notify` tinyint(1) NOT NULL,
  PRIMARY KEY  USING BTREE (`DeputyId`),
  KEY `DcUser` USING BTREE (`DcUser`),
  KEY `Deputy` USING BTREE (`Deputy`),
  CONSTRAINT `FK_deputy_1` FOREIGN KEY (`DcUser`) REFERENCES `DcUser` (`DcUserId`),
  CONSTRAINT `FK_deputy_2` FOREIGN KEY (`Deputy`) REFERENCES `DcUser` (`DcUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `docstatus`;
CREATE TABLE  `docstatus` (
  `DocStatusId` int(11) NOT NULL auto_increment,
  `Name` varchar(254) NOT NULL,
  PRIMARY KEY  (`DocStatusId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `eventtype`;
CREATE TABLE  `eventtype` (
  `EventTypeId` int(11) NOT NULL auto_increment,
  `Name` varchar(254) NOT NULL,
  PRIMARY KEY  (`EventTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `notificationtype`;
CREATE TABLE  `notificationtype` (
  `NotificationTypeId` int(11) NOT NULL auto_increment,
  `Name` varchar(254) NOT NULL,
  PRIMARY KEY  (`NotificationTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `foldertype`;
CREATE TABLE  `FolderType` (
  `FolderTypeId` int NOT NULL auto_increment,
  PRIMARY KEY  (`FolderTypeId`),
  `Name` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `newfolderpermission`;
CREATE TABLE `newfolderpermission` (
  `NewFolderPermissionId` INTEGER NOT NULL DEFAULT NULL AUTO_INCREMENT,
  `FolderType` INTEGER NOT NULL,
  `DcRole` INTEGER NOT NULL,
  `C` TINYINT(1) NOT NULL,
  `R` TINYINT(1) NOT NULL,
  `U` TINYINT(1) NOT NULL,
  `D` TINYINT(1) NOT NULL,
  `B` TINYINT(1) NOT NULL,
  `RO` TINYINT(1) NOT NULL,
  `UO` TINYINT(1) NOT NULL,
  `DO` TINYINT(1) NOT NULL,
  PRIMARY KEY (`NewFolderPermissionId`),
  INDEX FK_newfolderpermission_1 USING BTREE(`FolderType`),
  INDEX FK_newfolderpermission_2 USING BTREE(`DcRole`),
  CONSTRAINT `FK_newfolderpermission_1` FOREIGN KEY `FK_newfolderpermission_1` (`FolderType`)
    REFERENCES `foldertype` (`FolderTypeId`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_newfolderpermission_2` FOREIGN KEY `FK_newfolderpermission_2` (`DcRole`)
    REFERENCES `dcrole` (`DcRoleId`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS `newdoctypepermission`;
CREATE TABLE `newdoctypepermission` (
  `NewDocTypePermissionId` INTEGER NOT NULL AUTO_INCREMENT,
  `DcRole` INTEGER NOT NULL,
  `C` BOOLEAN NOT NULL,
  `R` BOOLEAN NOT NULL,
  `U` BOOLEAN NOT NULL,
  `D` BOOLEAN NOT NULL,
  `RO` BOOLEAN NOT NULL,
  `UO` BOOLEAN NOT NULL,
  `DO` BOOLEAN NOT NULL,
  PRIMARY KEY (`NewDocTypePermissionId`),
  CONSTRAINT `FK_newdoctypepermission_1` FOREIGN KEY `FK_newdoctypepermission_1` (`DcRole`)
    REFERENCES `dcrole` (`DcRoleId`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;


DROP TABLE IF EXISTS `folder`;
CREATE TABLE  `folder` (
  `FolderId` int(11) NOT NULL auto_increment,
  `ParentFolder` int(11) default NULL,
  PRIMARY KEY  (`FolderId`),
  KEY `Index_2` (`ParentFolder`),
  CONSTRAINT `FK_Folder_1` FOREIGN KEY (`ParentFolder`) REFERENCES `folder` (`FolderId`),
  `Name` varchar(254) NOT NULL,
  `FolderType` int NOT NULL,
  KEY `Index_3` (`FolderType`),
  CONSTRAINT `FK_Folder_2` FOREIGN KEY (`FolderType`) REFERENCES `FolderType` (`FolderTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `folderpermission`;
CREATE TABLE  `folderpermission` (
  `FolderPermissionId` int(11) NOT NULL auto_increment,
  `DcRole` int(11) NOT NULL,
  `Folder` int(11) NOT NULL,
  `C` bool NOT NULL,
  `R` bool NOT NULL,
  `U` bool NOT NULL,
  `D` bool NOT NULL,
  `B` bool NOT NULL,
  `RO` BOOLEAN NOT NULL,
  `UO` BOOLEAN NOT NULL,
  `DO` BOOLEAN NOT NULL,
  PRIMARY KEY  (`FolderPermissionId`),
  KEY `FK_folderpermission_2` (`DcRole`),
  KEY `FK_folderpermission_3` (`Folder`),
  CONSTRAINT `FK_folderpermission_2` FOREIGN KEY (`DcRole`) REFERENCES `dcrole` (`DcRoleId`),
  CONSTRAINT `FK_folderpermission_3` FOREIGN KEY (`Folder`) REFERENCES `folder` (`FolderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `doctype`;
CREATE TABLE  `doctype` (
  `DocTypeId` int(11) NOT NULL auto_increment,
  `Folder` int(11) NOT NULL,
  `DcUser` int(11) NOT NULL,
  `Name` varchar(254) NOT NULL default '',
  `Description` varchar(254) NOT NULL default '',
  `Prefix` varchar(45) NOT NULL default '',
  `NextNumber` int(11) NOT NULL default 1,
  PRIMARY KEY  (`DocTypeId`),
  KEY `FK_DocType_1` (`Folder`),
  KEY `FK_DocType_2` (`DcUser`),
  CONSTRAINT `FK_DocType_1` FOREIGN KEY (`Folder`) REFERENCES `folder` (`FolderId`),
  CONSTRAINT `FK_DocType_2` FOREIGN KEY (`DcUser`) REFERENCES `DcUser` (`DcUserId`),
  IsNew boolean not null default true
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `doctypeinfolder`;
CREATE TABLE  `doctypeinfolder` (
  `DocTypeInFolderId` int(11) NOT NULL,
  `Folder` int(11) NOT NULL,
  `DocType` int(11) NOT NULL,
  KEY `Index_1` (`DocType`),
  KEY `Index_2` (`Folder`),
  CONSTRAINT `FK_DocTypeInFolder_1` FOREIGN KEY (`Folder`) REFERENCES `folder` (`FolderId`),
  CONSTRAINT `FK_DocTypeInFolder_2` FOREIGN KEY (`DocType`) REFERENCES `doctype` (`DocTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `doctypepermission`;
CREATE TABLE  `doctypepermission` (
  `DocTypePermissionId` int(11) NOT NULL auto_increment,
  `DcRole` int(11) NOT NULL,
  `DocType` int(11) NOT NULL,
  `C` bool NOT NULL,
  `R` bool NOT NULL,
  `U` bool NOT NULL,
  `D` bool NOT NULL,
  `RO` BOOLEAN NOT NULL,
  `UO` BOOLEAN NOT NULL,
  `DO` BOOLEAN NOT NULL,
  PRIMARY KEY  (`DocTypePermissionId`),
  KEY `FK_doctypepermission_2` (`DcRole`),
  KEY `FK_doctypepermission_3` (`DocType`),
  CONSTRAINT `FK_doctypepermission_2` FOREIGN KEY (`DcRole`) REFERENCES `dcrole` (`DcRoleId`),
  CONSTRAINT `FK_doctypepermission_3` FOREIGN KEY (`DocType`) REFERENCES `doctype` (`DocTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `roletouser`;
CREATE TABLE  `roletouser` (
  `RoleToUserId` int(11) NOT NULL auto_increment,
  `DcRole` int(11) NOT NULL,
  `DcUser` int(11) NOT NULL,
  `Position` int(11) NOT NULL,
  PRIMARY KEY  (`RoleToUserId`),
  KEY `dcRoleId` USING BTREE (`DcRole`),
  KEY `dcUserId` USING BTREE (`DcUser`),
  CONSTRAINT `FK_roletouser_1` FOREIGN KEY (`DcRole`) REFERENCES `dcrole` (`DcRoleId`),
  CONSTRAINT `FK_roletouser_2` FOREIGN KEY (`DcUser`) REFERENCES `dcuser` (`DcUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `routepoint`;
CREATE TABLE  `routepoint` (
  `RoutePointId` int(11) NOT NULL auto_increment,
  `DocType` int(11) NOT NULL,
  `OrderNum` int(11) NOT NULL,
  `Expression` varchar(254) NOT NULL,
  PRIMARY KEY  (`RoutePointId`),
  KEY `FK_routepoint_1` (`DocType`),
  CONSTRAINT `FK_routepoint_1` FOREIGN KEY (`DocType`) REFERENCES `doctype` (`DocTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `routeitemtype`;
CREATE TABLE  `routeitemtype` (
  `RouteItemTypeId` int(11) NOT NULL auto_increment,
  `Name` varchar(45) NOT NULL,
  PRIMARY KEY  (`RouteItemTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `routeitem`;
CREATE TABLE  `routeitem` (
  `RouteItemId` int(11) NOT NULL auto_increment,
  `DocType` int(11) NOT NULL,
  `DcUser` int(11) NOT NULL,
  `RoutePoint` int(11) NOT NULL,
  `RouteItemType` int(11) NOT NULL,
  `ItemIndex` int(11) NOT NULL,
  PRIMARY KEY  (`RouteItemId`),
  KEY `FK_routeitem_1` (`DocType`),
  KEY `FK_routeitem_2` (`DcUser`),
  KEY `FK_routeitem_3` (`RoutePoint`),
  KEY `FK_routeitem_4` (`RouteItemType`),
  CONSTRAINT `FK_routeitem_1` FOREIGN KEY (`DocType`) REFERENCES `doctype` (`DocTypeId`),
  CONSTRAINT `FK_routeitem_2` FOREIGN KEY (`DcUser`) REFERENCES `dcuser` (`DcUserId`),
  CONSTRAINT `FK_routeitem_3` FOREIGN KEY (`RoutePoint`) REFERENCES `routepoint` (`RoutePointId`),
  CONSTRAINT `FK_routeitem_4` FOREIGN KEY (`RouteItemType`) REFERENCES `routeitemtype` (`RouteItemTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `useremail`;
CREATE TABLE  `useremail` (
  `UserEmailId` int(11) NOT NULL auto_increment,
  `DcUser` int(11) NOT NULL,
  `UserEmail` varchar(254) default NULL,
  PRIMARY KEY  (`UserEmailId`),
  KEY `dcUserId` USING BTREE (`DcUser`),
  CONSTRAINT `FK_useremail_1` FOREIGN KEY (`DcUser`) REFERENCES `dcuser` (`DcUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `institution`;
CREATE TABLE  `institution` (
  `InstitutionId` int(11) NOT NULL auto_increment,
  `DcUser` int(11) NOT NULL,
  `Name` varchar(254) default NULL,
  `Phone1` varchar(45) default NULL,
  `Phone2` varchar(45) default NULL,
  `Address1` varchar(254) default NULL,
  `Address2` varchar(254) default NULL,
  `Email1` varchar(45) default NULL,
  `Email2` varchar(45) default NULL,
  `WebSite` varchar(254) default NULL,
  `Note` text(2200) NOT NULL,
  PRIMARY KEY  (`InstitutionId`),
  KEY `FK_institution_1` (`DcUser`),
  CONSTRAINT `FK_institution_1` FOREIGN KEY (`DcUser`) REFERENCES `DcUser` (`DcUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `doccard`;
CREATE TABLE  `doccard` (
  `DocCardId` bigint(20) NOT NULL auto_increment,
  `Folder` int(11) NOT NULL,
  `DocType` int(11) NULL,
  `DocStatus` int(11) NOT NULL,
  `RegisteringUser` int(11) NOT NULL,
  `DcUser` int(11) NOT NULL,
  `LastMovement` datetime NULL,
  `Subject` varchar(254) NOT NULL,
  `InternalNumber` varchar(45) NOT NULL,
  `RegistrationTime` datetime NOT NULL,
  `FinalizingTime` datetime NULL,
  `AttachmentsAreChangeable` tinyint(1) NOT NULL,
  `OriginalDate` date NULL,
  `OriginalNumber` varchar(45) NOT NULL,
  `SendingCompany` int(11) NULL,
  `ReceivingCompany` int(11) NULL,
  `CustomSendingCompany` varchar(254) NOT NULL,
  `CustomReceivingCompany` varchar(254) NOT NULL,
  `Note` text(2200) NOT NULL,
  `RoutePointNum` int(11) NOT NULL,
  `IsNew` tinyint(1) NOT NULL,
  PRIMARY KEY  (`DocCardId`),
  KEY `FK_doccard_1` (`Folder`),
  KEY `FK_doccard_2` (`DocType`),
  KEY `FK_doccard_3` (`DocStatus`),
  KEY `FK_doccard_4` (`RegisteringUser`),
  KEY `FK_doccard_5` (`DcUser`),
  KEY `FK_doccard_6` (`SendingCompany`),
  KEY `FK_doccard_7` (`ReceivingCompany`),
  CONSTRAINT `FK_doccard_1` FOREIGN KEY (`Folder`) REFERENCES `folder` (`FolderId`),
  CONSTRAINT `FK_doccard_2` FOREIGN KEY (`DocType`) REFERENCES `doctype` (`DocTypeId`),
  CONSTRAINT `FK_doccard_3` FOREIGN KEY (`DocStatus`) REFERENCES `docstatus` (`DocStatusId`),
  CONSTRAINT `FK_doccard_4` FOREIGN KEY (`RegisteringUser`) REFERENCES `dcuser` (`DcUserId`),
  CONSTRAINT `FK_doccard_5` FOREIGN KEY (`DcUser`) REFERENCES `dcuser` (`DcUserId`),
  CONSTRAINT `FK_doccard_6` FOREIGN KEY (`SendingCompany`) REFERENCES `institution` (`InstitutionId`),
  CONSTRAINT `FK_doccard_7` FOREIGN KEY (`ReceivingCompany`) REFERENCES `institution` (`InstitutionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `attachment`;
CREATE TABLE  `attachment` (
  `AttachmentId` bigint(20) NOT NULL auto_increment,
  `DocCard` bigint(20) NOT NULL,
  `Name` varchar(254) NOT NULL,
  `Folder` bigint(20) NOT NULL,
  `File` bigint(20) NULL,
  `ContentType` varchar(254) NOT NULL,
  `Size` int(11) NOT NULL,
  `UploadTime` datetime NOT NULL,
  PRIMARY KEY  (`AttachmentId`),
  KEY `FK_attachment_1` (`DocCard`),
  CONSTRAINT `FK_attachment_1` FOREIGN KEY (`DocCard`) REFERENCES `doccard` (`DocCardId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `doccardreference`;
CREATE TABLE  `doccardreference` (
  `DocCardReferenceId` bigint(20) NOT NULL auto_increment,
  `MasterDocCard` bigint(20) NOT NULL,
  `SlaveDocCard` bigint(20) NOT NULL,
  PRIMARY KEY  (`DocCardReferenceId`),
  KEY `FK_doccardreference_1` (`MasterDocCard`),
  KEY `FK_doccardreference_2` (`SlaveDocCard`),
  CONSTRAINT `FK_doccardreference_1` FOREIGN KEY (`MasterDocCard`) REFERENCES `doccard` (`DocCardId`),
  CONSTRAINT `FK_doccardreference_2` FOREIGN KEY (`SlaveDocCard`) REFERENCES `doccard` (`DocCardId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `usernotification`;
CREATE TABLE  `usernotification` (
  `UserNotificationId` bigint(20) NOT NULL auto_increment,
  `DocCard` bigint(20) NULL,
  `NotificationType` int(11) NOT NULL,
  `DcUser` int(11) NOT NULL,
  `NotificationTime` datetime NOT NULL,
  `Text` text NOT NULL,
  `UserNote` text NOT NULL,
  `LastActionUser` int(11) NULL,
  `RoutePointNum` int(11) NOT NULL,
  PRIMARY KEY  (`UserNotificationId`),
  KEY `FK_usernotification_1` (`DocCard`),
  KEY `FK_usernotification_2` (`NotificationType`),
  KEY `FK_usernotification_3` (`DcUser`),
  CONSTRAINT `FK_usernotification_1` FOREIGN KEY (`DocCard`) REFERENCES `doccard` (`DocCardId`),
  CONSTRAINT `FK_usernotification_2` FOREIGN KEY (`NotificationType`) REFERENCES `notificationtype` (`NotificationTypeId`),
  CONSTRAINT `FK_usernotification_3` FOREIGN KEY (`DcUser`) REFERENCES `dcuser` (`DcUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `doccardrouteitem`;
CREATE TABLE  `doccardrouteitem` (
  `DocCardRouteItemId` bigint(20) NOT NULL auto_increment,
  `DocCard` bigint(20) NOT NULL,
  `RouteItem` int(11) NOT NULL,
  `Passed` tinyint(1) NOT NULL,
  PRIMARY KEY  (`DocCardRouteItemId`),
  KEY `FK_doccardrouteitem_1` (`DocCard`),
  KEY `FK_doccardrouteitem_2` (`RouteItem`),
  CONSTRAINT `FK_doccardrouteitem_1` FOREIGN KEY (`DocCard`) REFERENCES `doccard` (`DocCardId`),
  CONSTRAINT `FK_doccardrouteitem_2` FOREIGN KEY (`RouteItem`) REFERENCES `routeitem` (`RouteItemId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `event`;
CREATE TABLE  `event` (
  `EventId` bigint(20) NOT NULL auto_increment,
  `DocCard` bigint(20) NOT NULL,
  `EventType` int(11) NOT NULL,
  `DcUser` int(11) NOT NULL,
  `Deputy` int(11) NULL,
  `RoutePointNum` int(11) NOT NULL,
  `EventTime` datetime NOT NULL,
  `Note` text NOT NULL,
  `Text` text NOT NULL,
  PRIMARY KEY  (`EventId`),
  KEY `FK_event_1` (`DocCard`),
  KEY `FK_event_2` (`EventType`),
  KEY `FK_event_3` (`DcUser`),
  CONSTRAINT `FK_event_1` FOREIGN KEY (`DocCard`) REFERENCES `doccard` (`DocCardId`),
  CONSTRAINT `FK_event_2` FOREIGN KEY (`EventType`) REFERENCES `eventtype` (`EventTypeId`),
  CONSTRAINT `FK_event_3` FOREIGN KEY (`DcUser`) REFERENCES `dcuser` (`DcUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert data
-- Default roles: Administrators, Users
insert into DcRole values( default, "Administrators");
set @adminRole = last_insert_id();
insert into DcRole values( default, "Users");
set @userRole = last_insert_id();

-- Default department: Common
insert into Department (Name) Values ('Common');
set @commonDepartment = last_insert_id();


-- Default users: DocCircAdmin, DocCircService, DocCircUser
insert into DcUser values (default, "DocCircAdmin", PASSWORD("1"), "DocCirc Administrator", false, true, @commonDepartment, false, "", false, false, "");
set @lastUser = last_insert_id();
insert into UserEmail values (default, @lastUser, "doccircadmin@debug.org");
insert into RoleToUser values (default, @adminRole, @lastUser, 1);

insert into DcUser values (default, "DocCircService", PASSWORD("1"), "DocCirc service user", false, true, @commonDepartment, false, "", false, false, "");
set @lastUser = last_insert_id();
insert into UserEmail values (default, @lastUser, "doccircservice@debug.org");
insert into RoleToUser values (default, @adminRole, @lastUser, 1);

insert into DcUser values (default, "DocCircUser", PASSWORD("1"), "DocCirc user", false, true, @commonDepartment, false, "", false, false, "");
set @lastUser = last_insert_id();
insert into UserEmail values (default, @lastUser, "doccircuser@debug.org");
insert into RoleToUser values (default, @userRole, @lastUser, 1);

-- Folder types: Document, DocType
insert into FolderType (FolderTypeId, Name) Values (default, 'Document');
set @documentFolderType = last_insert_id();
insert into FolderType (FolderTypeId, Name) Values (default, 'DocType');
set @docTypeFolderType = last_insert_id();

-- RouteItemTypes: Decision, Notification
insert into RouteItemType (Name) Values ('Decision');
insert into RouteItemType (Name) Values ('Notification');

-- DocStatus: Document statuses
insert into DocStatus (Name) Values ('NotSavedDocStatus');
insert into DocStatus (Name) Values ('DraftDocStatus');
insert into DocStatus (Name) Values ('UnderConsDocStatus');
insert into DocStatus (Name) Values ('FinalizedDocStatus');

-- NotificationType: Notification types
insert into NotificationType (Name) Values ('NextDecision');
insert into NotificationType (Name) Values ('DeputyNextDecision');
insert into NotificationType (Name) Values ('NextNotification');
insert into NotificationType (Name) Values ('DecisionAfterReturn');
insert into NotificationType (Name) Values ('NotificationAfterReturn');
insert into NotificationType (Name) Values ('Finalization');
insert into NotificationType (Name) Values ('ReturnToDraftDueToDecision');
insert into NotificationType (Name) Values ('ReturnToDraftDueToRouteChange');
insert into NotificationType (Name) Values ('AcquaintanceRequest');

-- EventType: Event types
insert into EventType (Name) Values ('PassDecision');
insert into EventType (Name) Values ('PassDecisionAutomatically');
insert into EventType (Name) Values ('PassDecisionByDeputy');
insert into EventType (Name) Values ('RejectDecision');
insert into EventType (Name) Values ('RejectDecisionByDeputy');
insert into EventType (Name) Values ('RejectDecisionByAdministrator');
insert into EventType (Name) Values ('PostponeDecision');
insert into EventType (Name) Values ('PostponeDecisionByDeputy');
insert into EventType (Name) Values ('PostponeDecisionByAdministrator');
insert into EventType (Name) Values ('ReturnToDraftDueToRouteChange');
insert into EventType (Name) Values ('DocCardRegistration');
insert into EventType (Name) Values ('DocCardFinalization');
insert into EventType (Name) Values ('ConsiderationStarted');
insert into EventType (Name) Values ('PassedToNextRoutePoint');

-- permissions for new document type and document folder
insert into NewFolderPermission (NewFolderPermissionId, FolderType, DcRole, C, R, U, D, B, RO, UO, DO) Values
--										       C	  R     U	   D      B     RO    UO     DO	   
	(default, @docTypeFolderType, @adminRole, true,  true, true,  true,  true, true, true,  true),
	(default, @docTypeFolderType, @userRole,  false, true, false, false, true, true, false, false),
	(default, @documentFolderType, @adminRole,true,  true, true,  true,  true, true, true,  true),
	(default, @documentFolderType, @userRole, true,  true, true,  true,  true, true, true,  true);
	
-- permissions for new document type
insert into NewDocTypePermission (NewDocTypePermissionId, DcRole, C, R, U, D, RO, UO, DO) Values
--						   C	  R    U	  D    RO    UO    DO	 
	(default, @adminRole, true, true, true, true, true, true, true),
	(default, @userRole,  true, true, true, true, true, true, true);

-- Root folder for documents
insert into Folder (FolderId, ParentFolder, Name, FolderType) Values 
	(default, null, "^DocumentFolderName^", @documentFolderType);
set @documentRootFolder = last_insert_id();

insert into FolderPermission (Folder, DcRole, C, R, U, D, B, RO, UO, DO)
	select @documentRootFolder, DcRole, C, R, U, D, B, RO, UO, DO from NewFolderPermission where FolderType=@documentFolderType;

-- Root folder for document types
insert into Folder (FolderId, ParentFolder, Name, FolderType) 
	Values (default, null, "^DocTypeFolderName^", @docTypeFolderType);
set @docTypeRootFolder = last_insert_id();

insert into FolderPermission (Folder, DcRole, C, R, U, D, B, RO, UO, DO)
	select @docTypeRootFolder, DcRole, C, R, U, D, B, RO, UO, DO from NewFolderPermission where FolderType=@docTypeFolderType;

